﻿using System;
using System.Collections.Generic;
using System.Text;
using MISAPP.Common;
using System.Data;
using System.Linq;

namespace MISAPP.Classify.DataAccess
{
    public class DA_Matrix
    {

        #region property
        protected static object obj = null;
        protected string groupString = string.Empty;
        protected string extendFieldString = string.Empty; //group 上有的欄位都要放上來這裏
        protected string onString = string.Empty;
        protected string CriteriaString = string.Empty;
        protected string OrderByString = string.Empty;
        #endregion

        protected List<field> fieldList = null;
        protected List<string> whereList = null;
        protected string tableName = string.Empty;
        protected Dictionary<string, DbType> tableSchema = null;

        public enum DBOpt { MSSQL,Oracle,WMG,ARTM }

        //protected IDbCommand cmd = null;
        protected System.Data.Common.DbCommand cmd = null;

        /// <summary>
        /// 輸入SQL Statement 產生出 IDbCommand
        /// </summary>
        /// <param name="p_select">SQL Statement</param>
        protected void setCommand(string p_statement)
        {
            //cmd = Common.DAL.GetCommand();
            //cmd.CommandText = p_statement;
            setCommand(p_statement, DBOpt.MSSQL);
        }

        protected void setCommand(string p_statement,DBOpt p_opt)
        {
            cmd = Common.DAL.GetCommand(p_opt.ToString());
            cmd.CommandText = p_statement;
        }


        /// <summary>
        /// 輸入查詢語法，取回DataTable
        /// </summary>
        /// <param name="p_select">Select Statement</param>
        /// <param name="p_table">Table Name</param>
        /// <returns></returns>
        protected DataTable getData(string p_statement, string p_table)
        {
            setCommand(p_statement);
            DataTable dt = Common.DAL.GetDataTable(cmd);
            dt.TableName = p_table;
            return dt;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="p_select">Select Statement</param>
        /// <param name="p_table">Table Name</param>
        /// <param name="p_opt">DBOpt</param>
        /// <returns></returns>
        protected DataTable getData(string p_statement, string p_table
            ,DBOpt p_opt)
        {
            setCommand(p_statement,p_opt);
            DataTable dt = Common.DAL.GetDataTable(cmd);
            dt.TableName = p_table;
            return dt;
        }


        /// <summary>
        /// 傳入 DBCommand，取回DataTable
        /// </summary>
        /// <param name="p_cmd">已含Select Statement</param>
        /// <returns></returns>
        protected DataTable getData(IDbCommand p_cmd)
        {
            return Common.DAL.GetDataTable(p_cmd);
        }

        protected DataTable getData()
        {
            return getData(cmd);
        }

        protected virtual void FillTableSchema(string p_tableName)
        {
            if (p_tableName.IndexOf('.') > 0)
            {
                p_tableName = p_tableName.Substring(p_tableName.LastIndexOf('.') + 1);
            }
            tableSchema = null;
            string sql = @"
                            SELECT A.name as fldName ,b.name as typeName
                            FROM(
                                    SELECT object_id
                                        ,name
                                        ,system_type_id 
                                    FROM sys.all_columns WHERE object_id IN
                                    (
                                        SELECT object_id FROM sys.all_objects WHERE [name]=@p_tableName
                                    )
                                )AS A 
                                LEFT OUTER JOIN sys.systypes B 
                                    ON A.system_type_id = b.xtype
                                WHERE B.status=0";
            
            cmd = Common.DAL.GetCommand();
            cmd.CommandText = sql;
            cmd.Parameters.Add(DAL.GetParameter("@p_tableName", DbType.String, p_tableName,  cmd.CreateParameter()));
            DataTable dt = getData(cmd);

            if (dt.Rows.Count == 0)
            {
                return;
            }
            tableSchema = new Dictionary<string, DbType>();
            for(int i=0 ;i<dt.Rows.Count ;i++)
            {
                tableSchema.Add(dt.Rows[i]["fldName"].ToString()
                    , transformDBtypeFromField(dt.Rows[i]["typeName"].ToString())
                    );
            }
        }


        DbType transformDBtypeFromField(string p_fieldType)
        {
            DbType dbType = DbType.String;
            switch (p_fieldType)
            { 
                case "float":
                    dbType = DbType.Single;
                    break;
                case "date":
                    dbType = DbType.Date;
                    break;
            }
            return dbType;
        }

        /// <summary>
        /// 取得該欄位名對應到的 ado.net 的DbType
        /// </summary>
        /// <param name="p_field">欄位名稱</param>
        /// <returns></returns>
        protected DbType GetFieldDbType(string p_field)
        {
            return tableSchema[p_field];
        }

        public virtual string[] GetDistinctField()
        { 
            return new string[]{""};
        }
       
        public virtual DataSet GetCriteria()
        {
            string[] criterias = GetDistinctField();
            string sql = "SELECT DISTINCT {1} FROM {0} ORDER BY 1 DESC";
            DataSet ds = new DataSet();
            DataTable dt = null;

            foreach (string fld in criterias)
            {
                dt = getData(string.Format(sql, tableName, fld)
                                , fld);
                ds.Tables.Add(dt);
            }
            return ds;
        }

        public virtual DataTable GetGroupBy(string p_team,string p_tblName)
        {
            string sql = string.Format(@"SELECT fldname, CASE WHEN (fldvalue IS NULL) OR
                                            (fldvalue = '') THEN fldname ELSE fldvalue END AS fldvalue, team
                                            FROM MISParameter
                                            WHERE 1=1 
                                            AND (para1 = 'GROUPBY') 
                                            AND (team = '{0}') ORDER BY SEQ ",p_team);
            return getData(sql, p_tblName);
        }

        protected virtual void AddParameter(string p_sql)
        {
            setCommand(p_sql);
            AddParameter();
        }

        protected virtual void AddParameter(string p_sql,DBOpt p_opt)
        {
            setCommand(p_sql,p_opt);
            //AddParameter();
            foreach (field fld in fieldList)
            {
                cmd.Parameters.Add(DAL.GetParameter(
                    p_opt.Equals(DBOpt.Oracle)
                        ? fld.ParameterName.Replace('@', ':') 
                        : fld.ParameterName
                    , fld.dbType
                    , fld.fieldValue
                    , cmd.CreateParameter()));
            }
        }

        private void AddParameter()
        {
            foreach (field fld in fieldList)
            {
                cmd.Parameters.Add(DAL.GetParameter(
                    fld.ParameterName
                    , fld.dbType
                    , fld.fieldValue
                    , cmd.CreateParameter()));
            }
        }

        /*
         protected virtual void FillFieldList(string[] p_valueArray, string[] p_parmArray, string p_fieldName)
        {
            if (fieldList == null)
            {
                fieldList = new List<field>();
            }
            for (int i = 0; i < p_valueArray.Length; i++)
            {
                fieldList.Add(new field(p_parmArray[i], GetFieldDbType(p_fieldName), p_valueArray[i].ToString()));
            }
        }
         */

        protected virtual void FillFieldList(object[] p_valueArray, string[] p_parmArray, string p_fieldName)
        {
            if (fieldList == null)
            {
                fieldList = new List<field>();
            }
            for (int i = 0; i < p_valueArray.Length; i++)
            {
                fieldList.Add(new field(p_parmArray[i], GetFieldDbType(p_fieldName), p_valueArray[i]));
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="p_valueArray"></param>
        /// <param name="p_parmArray"></param>
        /// <param name="p_fieldName"></param>
        protected virtual void FillFieldList(string[] p_valueArray, string[] p_parmArray
            , string[] p_fieldName)
        {
            if (fieldList == null)
            {
                fieldList = new List<field>();
            }
            for (int i = 0; i < p_valueArray.Length; i++)
            {
                fieldList.Add(new field(p_parmArray[i], GetFieldDbType(p_fieldName[i]), p_valueArray[i].ToString()));
            }
        }



        /// <summary>
        /// 給有 In string 的條件來使用
        /// </summary>
        /// <param name="p_fieldName"></param>
        /// <param name="p_inString"></param>
        /// <param name="p_values"></param>
        public virtual void AddCriteria(string p_fieldName, string p_inString, string[] p_values)
        {
            var parms = p_values.Select((s, i) => string.Format("@{0}{1}", p_fieldName, i.ToString())).ToArray();
            var inclause = string.Join(",", parms);
            FillFieldList(p_values, parms, p_fieldName);
            AddWhereList(string.Format(" AND {0} {1} ({2})"
                             , p_fieldName, p_inString, inclause));
        }

        /// <summary>
        /// 給 Detail 用
        /// </summary>
        /// <param name="p_selectKey"></param>
        public virtual void AddCriteria2(Dictionary<string, string> p_selectKey)
        {
            OrderByString = string.Empty;
            StringBuilder sb = new StringBuilder();
            StringBuilder ordSB = new StringBuilder();
            foreach (KeyValuePair<string, string> key in p_selectKey)
            {
                ordSB.AppendFormat(string.Format(",{0}", key.Key.ToString()));
                sb.AppendFormat(" AND {0}=@{0} ", key.Key.ToString());
                fieldList.Add(new field("@" + key.Key.ToString()
                    , GetFieldDbType(key.Key.ToString())
                    , key.Value.ToString()));
            }
            OrderByString = string.Format(" ORDER BY {0} ", ordSB.ToString().Substring(1));
            AddWhereList(sb.ToString());
        }

        public virtual string GetInString(bool p_isReverse)
        {
            return p_isReverse ? "NOT IN" : "IN";
        }

        public virtual void ResetCriteria()
        {
            whereList = new List<string>();
            fieldList = new List<field>();
            FillTableSchema(tableName);
        }

        protected virtual void AddWhereList(string p_string)
        {
            if (whereList == null)
            {
                whereList = new List<string>();
            }
            whereList.Add(p_string);
        }

        protected virtual void GetCriteriaString()
        {
            //StringBuilder criteria = new StringBuilder();
            //if (whereList == null)
            //{
            //    criteria.AppendFormat("{0}", "");
            //}
            //else
            //{
            //    foreach (string whr in whereList)
            //    {
            //        criteria.AppendFormat("{0}", whr);
            //    }
            //}
            //CriteriaString = criteria.ToString();
            this.GetCriteriaString(DBOpt.MSSQL);
        }

        protected virtual void GetCriteriaString(DBOpt p_opt)
        {
            StringBuilder criteria = new StringBuilder();
            if (whereList == null)
            {
                criteria.AppendFormat("{0}", "");
            }
            else
            {
                foreach (string whr in whereList)
                {
                    criteria.AppendFormat("{0}", whr);
                }
            }
            if (p_opt.Equals(DBOpt.Oracle))
            {
                CriteriaString = criteria.ToString().Replace('@',':');
            }
            else
            {
                CriteriaString = criteria.ToString();
            }            
        }


        /// <summary>
        /// 要先取到Group，才能在這Parse
        /// </summary>
        protected virtual void GetOnString()
        {
            StringBuilder sb = new StringBuilder();
            if (groupString != string.Empty)
            {
                string[] ons = groupString.Split(',');
                foreach (string str in ons)
                {
                    sb.AppendFormat(",(A.{0} = B.{0})", str);
                }
            }
            onString = sb.ToString().Substring(1).Replace(",", " AND ");
        }

        protected virtual void GetGroupByString(string p_groupBy)
        {
            groupString = p_groupBy;
            extendFieldString = ",A." + groupString.Replace(",", ",A.");
            GetOnString();
        }

        /// <summary>
        /// 取得排除公庫，如果為真表示排除公庫，反之則要包含公庫
        /// 預設為排除公庫
        /// </summary>
        /// <returns></returns>
        protected String GetExclusiveOfAccount(bool p_exclusiveOf)
        {
           // return p_exclusiveOf == true ? " AND LEFT(帳號,3)<>'411' " : " AND 1=1 ";
            //return p_exclusiveOf == true ? " AND NOT 帳號 LIKE  '411%' " : " AND 1=1 ";
            return string.Format("AND {0} 帳號 LIKE '411%' "
                , p_exclusiveOf == true ? "NOT" : "");
        }

        protected DataTable getSquaredAreaByLevel(string p_team, string p_level)
        {
            return Utility.GetParameterByLevel(p_team, p_level);
        }
    }
}
